package net.gd.servlet;

/**
 * 数据主题的基本功能
 */

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import net.gd.dao.DBPermissionDAO;
import net.gd.domain.dbManage;
import net.gd.domain.dbPermissionDomain;
import net.gd.domain.modelManage;
import net.gd.domain.roleDetail;
import net.gd.domain.roleTopicRel;
import net.gd.domain.topicDetail;
import net.gd.dto.dbManageDto;
import net.gd.dto.modelManageDto;
import net.gd.service.CommonService;
import net.gd.service.dbPermissionService;
import net.gd.utils.Constants;
import net.gd.utils.CustomizeSpringContext;
import net.gd.utils.DAOFactory;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


/**
 * abm dev的etl流程
 * 
 * @author think
 *
 */
public class PermissionServlet extends BaseServlet {
	
	private static final long serialVersionUID = 1L;
	private static Logger logger = Logger.getLogger(PermissionServlet.class);
	private dbPermissionService dbPermissionService = (dbPermissionService) CustomizeSpringContext.getBean("dbPermissionService");
	private CommonService commonService =(CommonService) CustomizeSpringContext.getBean("commonService");
	//每页显示的行数
	static Integer PAGESIZE;
	//最大展示记录数
	final static Integer RECORDSIZE = 3000;

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doPost(req, resp);
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String path = req.getRequestURI();
        //当前操作时间
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = format.format(Calendar.getInstance().getTime());
        if(path.indexOf("permission/addTp") != -1){ // 新增主题
        	addTp(req, resp,time,"addTp");
        	
        }else if(path.indexOf("permission/updateTp") != -1){ // 更新主题
        	updateTp(req, resp,time,"updateTp");
        	
        }else if(path.indexOf("permission/deleteTp") != -1){ // 删除主题
        	deleteTp(req, resp,time,"deleteTp");
        	
        }else if(path.indexOf("permission/selectTp") != -1){ // 获得主题信息
        	selectTp(req, resp,time,"selectTp");
        	
        }else if (path.indexOf("permission/insertPM") != -1) { // 保存主题和表的权限关系
			inDbPermission(req, resp,time,"insertPM");
			
		} else if (path.indexOf("permission/upPMTopic") != -1) { // 更新主题和表的权限关系
			upPermission(req, resp,time,"upPMTopic");
			
		}else if (path.indexOf("permission/deletePMTopic") != -1) { // 删除主题和表的权限关系
			deletePermission(req, resp,time,"deletePMTopic");
			
		}else if (path.indexOf("permission/upAuthority") != -1){
			upAuthority(req, resp,time,"upAuthority");
			
		}else if (path.indexOf("permission/selectPM") != -1){ // 获得主题和表的权限关系
			searchTablesPM(req, resp,time,"selectPM");
			
		}else if(path.indexOf("permission/selectImportTables") != -1){ // 获得现有导入系统的表
			selectImportTables(req, resp,time,"selectImportTables");
			
		}else if(path.indexOf("permission/selectRoles") != -1){
			selectRoles(req, resp,time,"selectRoles");
			
		}else if(path.indexOf("permission/addRole") != -1){
			addRole(req, resp,time,"addRole");
			
		}else if(path.indexOf("permission/deleteRole") != -1){
			deleteRole(req, resp,time,"deleteRole");
			
		}else if(path.indexOf("permission/updateRole") != -1){
			updateRole(req, resp,time,"updateRole");
			
		}else if(path.indexOf("permission/roleAddTp") != -1){
			roleAddTp(req, resp,time,"updateRole");
			
		}else if(path.indexOf("permission/delRoleRel") != -1){
			deleteRoleRel(req, resp,time,"deleteRoleRel");
			
		}else if(path.indexOf("permission/getPermissionId") != -1){
			getPermissionId(req, resp,time,"getPermissionId");
		}
      
	}

	/**
	 * 新增主题(一次新增一个主题)
	 */
	public void addTp(HttpServletRequest request, HttpServletResponse response,String time,String opType){
		//判断管理员角色
		String operRole = request.getParameter("operRole");
		String userId = request.getParameter("userId");
//		String roleId = request.getParameter("roleId");
		String topicName = request.getParameter("topicName");
		String topicDesc = request.getParameter("topicDesc");

		String topicTp = request.getParameter("TopicTp");
		String topicId = UUID.randomUUID().toString();
		
		
		String message = "";
		String status = "0";
		JSONObject resultObj = new JSONObject();
		
		// 只允许管理添加主题
//		if(!"0".equals(operRole)){
//			status = "1";
//			logger.info(operRole+ " have no authority to update table topicDetail ");
//			message = operRole + " have no authority to update table topicDetail ";
//			resultObj.accumulate("status", status);
//			resultObj.accumulate("message", message);
//			saveInfo4Ajax(request, response, resultObj.toString());
//			return;
//		}
		
		// 判断主题是否已经存在，UUID随机生成出现重复的可能性？
//		List<topicDetail> topicList = dbPermissionService.selectTopicList(topicId);
		// 判断主题是否存在，除了Id不能重复外，主题类型、主题名称是不是也不应该重复？
		topicDetail topicList = dbPermissionService.selectTopicList(topicTp, topicName);
		
		if(topicList == null){
			topicDetail topicDetail = new topicDetail();
			topicDetail.setTopicid(topicId);
			topicDetail.setTopicname(topicName);
			topicDetail.setTopicdesc(topicDesc);
			topicDetail.setCreatetime(time);
			topicDetail.setTopictype(topicTp);
			
			dbPermissionService.addTopic(topicDetail);
		}else{
			status = "1";
			message = "主题已存在，请重试！";
			logger.error(message);
		}
		
		// 检查角色-主题关系是否存在
		roleTopicRel roleTopicList = dbPermissionService.selectRoleTopicList(operRole, topicId);
		if(roleTopicList == null){
			roleTopicRel roleTopic = new roleTopicRel();
			roleTopic.setId(UUID.randomUUID().toString());
			roleTopic.setRoleid(operRole);
			roleTopic.setTopicid(topicId);
			
			dbPermissionService.addRoleTopicRel(roleTopic);
		}else{
			logger.info("新增主题："+topicId+"，操作角色："+operRole);
			message = "主题和当前的角色关系已存在，不影响主题新建！";
		}
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		
		dbPermissionService.insertLog(operRole, userId, opType, time, status, message);

		saveInfo4Ajax(request, response, resultObj.toString());
	}
	
	
	
	/**
	 * 修改主题
	 * @param request
	 * @param response
	 * @param time
	 * @param opType
	 * @throws IOException 
	 */
	public void updateTp(HttpServletRequest req, HttpServletResponse resp,String time,String opType) throws IOException{
		String operRole = req.getParameter("operRole");
		String userId = req.getParameter("userId");
		String topicID = req.getParameter("topicId");
		String topicTp = req.getParameter("topicTp");
		String topicDesc = req.getParameter("topicDesc");
		String topicName = req.getParameter("topicName");
		
		String status = "0";
		String message = "";
		JSONObject resultObj = new JSONObject();
		
		// 只允许管理员进行操作
//		if(!"0".equals(operRole)){
//			status = "1";
//			logger.info(operRole+ " have no authority to update table topicDetail ");
//			message = operRole + " have no authority to update table topicDetail ";
//			resultObj.accumulate("status", status);
//			resultObj.accumulate("message", message);
//			saveInfo4Ajax(req, resp, resultObj.toString());
//			return;
//		}
		
		topicDetail topicList = dbPermissionService.selectTopicList(topicTp, topicName);
		if(topicList == null){
			status = "1";
			message = "主题不存在";
			logger.error(topicName + message);
		}else{
			dbPermissionService.deleteTopic(topicID);
			dbPermissionService.addTopic(topicID, topicName, topicTp, topicDesc, time);
			message = "更新主题："+topicName;
			logger.info(topicName + message);
		}
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		
		dbPermissionService.insertLog(operRole, userId, opType, time, status, message);
		
		saveInfo4Ajax(req, resp, resultObj.toString());
		logger.info(message);
		
	}
	
	/**
	 * 删除主题
	 * @param request
	 * @param response
	 * @param time
	 * @param opType
	 * @throws IOException 
	 */
	public void deleteTp(HttpServletRequest req, HttpServletResponse resp,String time,String opType) throws IOException{
		//判断管理员角色
		String message = "";
		String operRole = req.getParameter("operRole");
		String userId = req.getParameter("userId");
		String topicID = req.getParameter("curTopicId");

		//TODO 管理员ID确认
		String status = "0";
		JSONObject resultObj = new JSONObject();
		if(!"0".equals(operRole)){
			status = "1";
			logger.info(operRole+ " have no authority to update table topicDetail ");
			message = operRole + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(req, resp, resultObj.toString());
			return;
		}

		// 判断当前主题是否已经被角色使用
		
		List<roleTopicRel> roleTopicList = dbPermissionService.selectRoleTopicListByTopic(topicID);
		
		List<dbPermissionDomain> dbPermissionList = dbPermissionService.selectTopicTablRel(topicID);
		
		if(roleTopicList.isEmpty()){
			if(dbPermissionList.isEmpty()){
				List<topicDetail> topicList = dbPermissionService.selectTopicList(topicID);
				if(topicList.isEmpty()){
					status = "1";
					message = "该主题已不存在！";
				}else{
					dbPermissionService.deleteTopic(topicID);
				}
			}else{
				status ="0";
				message = "该主题已被关联，关联表的主题会被修改为Default！";
				String newTopicId = "0";
				
				for(dbPermissionDomain dbPermission : dbPermissionList){
					dbPermissionDomain db = new dbPermissionDomain();
					db.setId(dbPermission.getId()); 
					db.setDbname(dbPermission.getDbname());
					db.setTablename(dbPermission.getTablename());
					db.setRdpm(dbPermission.getRdpm());
					db.setWtpm(dbPermission.getWtpm());
					db.setTopicid(newTopicId);
					dbPermissionService.deleteTopicTblRel(dbPermission.getId());
					dbPermissionService.addTopicTblRel(db);
				}
			}
		}else{
			status ="1";
			message = "该主题已被角色使用，请先释放主题！";
		}
		
		dbPermissionService.insertLog(operRole, userId, opType, time, status, message);
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		saveInfo4Ajax(req, resp, resultObj.toString());
		logger.info(message);
		
	}
	
	/**
	 * 查询主题
	 * @param request
	 * @param response
	 * @param time
	 * @param opType
	 */
	public void selectTp(HttpServletRequest request, HttpServletResponse response,String time,String opType){
		String roleId = request.getParameter("operRole");
		String topicTp = request.getParameter("topicTp");
		String userId = request.getParameter("userId");
		String status = "0";
		String message = "";
		
		JSONObject resultObj = new JSONObject();
		List<topicDetail> topicDetatilList = new ArrayList<topicDetail>();
		// 根据主题类型查询主题信息，主题类型为空时显示全部主题
		if(StringUtils.isEmpty(topicTp)){
			topicDetatilList = dbPermissionService.selectTopicIdList(roleId);
		}else{
			topicDetatilList = dbPermissionService.selectTopicIdList(roleId, topicTp);
		}
		
		// 没有分页信息的时候，显示全部主题数据
		String current = request.getParameter("current"); //当前页码，从1开始
		if(!StringUtils.isEmpty(current)){
			PAGESIZE = Integer.parseInt(request.getParameter("pagesize")); //每页显示条数
			
			int page = 0;
			if(!StringUtils.isEmpty(current) && !current.equals("undefined")){
				page = Integer.parseInt(request.getParameter("current"));
			}
			resultObj = commonService.jsonProcessing(JSONArray.fromObject(topicDetatilList), page, current, PAGESIZE);
		}else{
			resultObj.accumulate("result", topicDetatilList);
		}
		
		dbPermissionService.insertLog(roleId, userId, opType, time, status, message);
		
		resultObj.accumulate("status", status);
		
		saveInfo4Ajax(request, response, resultObj.toString());
		
	}
	/**
	 * 主题增加表记录
	 * 支持批量
	 * table_rdPM_wtPM：表名_读权限_写权限（0：有权限 1 没有）
	 * @param req
	 * @param resp
	 * @return
	 * @throws IOException
	 * @throws Exception
	 */
	public void inDbPermission(HttpServletRequest request, HttpServletResponse response,String time,String opType){
		String userId = request.getParameter("userId");
		String roleId = request.getParameter("operRole");
		String topicId = request.getParameter("topicId");
		String[] tableRdPMs = request.getParameter("db_table_rdPM_wtPM").split(",");
		
		String message = "为主题新增表-权限关系";
		String status = "0";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(roleId)){
			status = "1";
			logger.info(roleId+ " have no authority to update table topicDetail ");
			message = roleId + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(request, response, resultObj.toString());
			return;
		}
		
		for(int i=0;i < tableRdPMs.length; i++){
			dbPermissionDomain dbPermission = new dbPermissionDomain();
			String dbName = tableRdPMs[i].split("_")[0];
			String tableName = tableRdPMs[i].split("_")[1];
			int rdPM = Integer.valueOf(tableRdPMs[i].split("_")[2]);
			int wtPM = Integer.valueOf(tableRdPMs[i].split("_")[3]);
			dbPermission.setTopicid(topicId);
			dbPermission.setDbname(dbName);
			dbPermission.setTablename(tableName);
			dbPermission.setRdpm(rdPM);
			dbPermission.setWtpm(wtPM);
			
			//判断主题-表关系是否已经存在：topicId、db、tb
			
			dbPermissionDomain permission = dbPermissionService.selectDbPermission(topicId, dbName, tableName);
			if(permission != null){
				int id = permission.getId();
				dbPermissionService.deleteDbPermissionById(id);
			}
			dbPermissionService.addDbPermission(dbPermission);
		}
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		dbPermissionService.insertLog(roleId, userId, opType, time, status, message);

		saveInfo4Ajax(request, response, resultObj.toString());
		logger.info(message);
	}
	

	
	/**
	 * 修改主題
	 * 支持批量修改数据表的主题
	 * @param req
	 * @param resp
	 * @return
	 * @throws IOException
	 */
	public void upPermission(HttpServletRequest request, HttpServletResponse response,String time,String opType) throws IOException{
		//判断管理员角色
		String[] permissionId = request.getParameter("permissionId").split(",");
		String userId = request.getParameter("userId");
		String roleId = request.getParameter("operRole");
		String topicId = request.getParameter("topicId");
		String[] tableRdPMs = request.getParameter("db_table_rdPM_wtPM").split(",");
		
		String message = "更新主题新增表-权限关系";
		String status = "0";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(roleId)){
			status = "1";
			logger.info(roleId+ " have no authority to update table topicDetail ");
			message = roleId + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(request, response, resultObj.toString());
			return;
		}
		
		for(int i=0;i < permissionId.length; i++){
			int id = Integer.valueOf(permissionId[i]);
			List<dbPermissionDomain> dbPermissionList = dbPermissionService.selectDbPermissionById(id);
			if(!dbPermissionList.isEmpty()){
				dbPermissionService.deleteDbPermissionById(id);
			}
			dbPermissionDomain dbPermission = new dbPermissionDomain();
			String dbName = tableRdPMs[i].split("_")[0];
			String tableName = tableRdPMs[i].split("_")[1];
			int rdPM = Integer.valueOf(tableRdPMs[i].split("_")[2]);
			int wtPM = Integer.valueOf(tableRdPMs[i].split("_")[3]);
			dbPermission.setTopicid(topicId);
			dbPermission.setDbname(dbName);
			dbPermission.setTablename(tableName);
			dbPermission.setRdpm(rdPM);
			dbPermission.setWtpm(wtPM);
			dbPermission.setId(id);
			dbPermissionService.addDbPermission(dbPermission);
		}

		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		dbPermissionService.insertLog(roleId, userId, opType, time, status, message);

		saveInfo4Ajax(request, response, resultObj.toString());
		logger.info(message);
	}
	
	/**
	 * @description 删除表权限关系 
	 * @author Uriel
	 * @date 2018年2月5日 上午4:24:57
	 * @version V3.0
	 * @param request
	 * @param response
	 * @param time
	 * @param opType
	 * @throws IOException
	 */
	public void deletePermission(HttpServletRequest request, HttpServletResponse response,String time,String opType) throws IOException{
		//判断管理员角色
		String[] permissionId = request.getParameter("permissionId").split(",");
		String userId = request.getParameter("userId");
		String roleId = request.getParameter("operRole");
		
		String message = "更新主题新增表-权限关系";
		String status = "0";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(roleId)){
			status = "1";
			logger.info(roleId+ " have no authority to update table topicDetail ");
			message = roleId + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(request, response, resultObj.toString());
			return;
		}
		
		for(int i=0;i < permissionId.length; i++){
			int id = Integer.valueOf(permissionId[i]);
			List<dbPermissionDomain> dbPermissionList = dbPermissionService.selectDbPermissionById(id);
			if(!dbPermissionList.isEmpty()){
				dbPermissionService.deleteDbPermissionById(id);
			}else{
				message = "表-权限关系已经不存在！";
			}
		}

		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		dbPermissionService.insertLog(roleId, userId, opType, time, status, message);

		saveInfo4Ajax(request, response, resultObj.toString());
		logger.info(message);

	}
	
	/**
	 * @description 查询数据主题类型下某一主题对应的数据表 
	 * @author Uriel
	 * @date 2018年2月8日 下午3:25:22
	 * @version V3.0
	 * @param req
	 * @param resp
	 * @param time
	 * @param opType
	 */
	public void selectImportTables(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		//判断管理员角色
		String topicId = req.getParameter("topicId");
		String topicTp = req.getParameter("topicTp");
//		String roleId = req.getSession().getAttribute("operRole").toString();
//		String userId = req.getSession().getAttribute("userId").toString();
//		String userName = req.getSession().getAttribute("userName").toString();
		String userId =req.getParameter("userId");
		String roleId = req.getParameter("operRole");
//		String topicType = req.getParameter("topicTp");
		
		String status = "0";
		String message = "";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(roleId)){
			status = "1";
			logger.info(roleId+ " have no authority to update table topicDetail ");
			message = roleId + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(req, resp, resultObj.toString());
			return;
		}
		
		String current = req.getParameter("current"); //当前页码，从1开始
		
		// 判断当前主题是否已经存在主题-关系被绑定
		JSONArray dbArr = new JSONArray();
		JSONArray modelArr = new JSONArray();
		
		List<dbPermissionDomain> permissionList = dbPermissionService.selectDbPermission(topicId);
		// 当前主题没有关联的表
		// 获得全部的数据主题表
		if("0".equals(topicTp) || StringUtils.isEmpty(topicTp)){
			List<dbManage> dbTbList = dbPermissionService.selectDbList();
			for(int j=0;j< dbTbList.size();j++){
				JSONObject rsObj = new JSONObject();
				dbManage dbTb = dbTbList.get(j);
				String dbName = dbTb.getDatabaseName();
				String tbName = dbTb.getSourceTable();
				System.out.println("1="+dbName+":"+tbName);
				
				int flag = 0;
				for(int i=0;i<permissionList.size();i++){
					dbPermissionDomain permission = permissionList.get(i);
					String perDbName = permission.getDbname();
					String perTbName = permission.getTablename();
					System.out.println("2="+perDbName+":"+perTbName);
					
					if(perDbName.equals(dbName) && perTbName.equals(tbName)){
						flag = 1;
					}
				}
				if(flag == 0){
					rsObj.accumulate("dbName", dbName);
					rsObj.accumulate("tbName", tbName);
					dbArr.add(rsObj);
				}
			}
		}
		
		// 获得最后输出并导出表的模型记录
		if("1".equals(topicTp) || StringUtils.isEmpty(topicTp)){
			List<modelManage> modelList = dbPermissionService.selectDistinctModel();
			for(int k=0;k< modelList.size();k++){
				JSONObject modelobj = new JSONObject();
				modelManage model = modelList.get(k);
				String tbName = model.getOutputSourcename();
				String modelId = model.getModeId();
				
				int sign = 0;
				for(int i=0;i<permissionList.size();i++){
					dbPermissionDomain permission = permissionList.get(i);
					String perDbName = permission.getDbname();
					String perTbName = permission.getTablename();
					
					if(perDbName.equals("processing") && perTbName.equals(tbName)){
						sign = 1;
					}
				}
				
				if(sign == 0){
					modelobj.accumulate("modelId", modelId);
					modelobj.accumulate("dbName", "proccessing");
					modelobj.accumulate("tbName", tbName);
					modelArr.add(modelobj);
				}
			}
		}

		JSONObject dataObj = new JSONObject();
		dataObj.accumulate("dbList", dbArr);
		dataObj.accumulate("modelList", modelArr);
		if(StringUtils.isEmpty(current)){
			resultObj.accumulate("result", dataObj);
		}else{
			PAGESIZE = Integer.parseInt(req.getParameter("pagesize")); //每页显示条数
			int page = 0;
			if(!StringUtils.isEmpty(current) && !current.equals("undefined")){
				page = Integer.parseInt(req.getParameter("current"));
			}
			resultObj = commonService.jsonProcessing(dbArr, page, current, PAGESIZE);
		}
		message = "获得当前主题下未绑定的数据主题表";
		logger.info("select table DBMANAGE");
		
		resultObj.accumulate("status", status);
		dbPermissionService.insertLog(roleId, userId, opType, time, status, message);

		saveInfo4Ajax(req, resp, resultObj.toString());
	}
	
	/**
	 * 系统管理员修改权限
	 * @param req
	 * @param resp
	 * @param time
	 * @param opType
	 */
	public void upAuthority(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		//判断管理员角色
		String roleId = req.getParameter("roleId");
		String message = "";
		String status = "";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(roleId)){
			status="1";
			logger.info("have no authority to update table upAuthority");
			message = "have no authority to update table upAuthority";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveError4Ajax(req, resp, resultObj.toString());
			return;
		}
		String userId = req.getParameter("userId");
		String topicId = req.getParameter("topicId");
		String dbName = req.getParameter("dbName");
		String tableRdPM = req.getParameter("table_rdPM_wtPM");

		String details = "";
		DBPermissionDAO dBPermissionDAO = DAOFactory.getDBPermissionDAO();
		String sql = "";
		if (null == roleId || "" == roleId || null == topicId || "" == topicId 
			|| "" == userId || null == userId || null == dbName || "" == dbName 
			|| "" == tableRdPM || null == tableRdPM) {
			status = "1";
			details = "roleId  or user_id or topic or tableName is invalide!";
			message = "roleId  or user_id or topic or tableName is invalide ,please connect with system manager!";
		} else {
			//判断参数个数
			String[] split = tableRdPM.split("_", 3);
			if(3 != split.length){
				details = "update table upAuthority,table_rdPM_wtPM lentgh is invalide, length :"+split.length;
				status = "1";
				message = "table_rdPM_wtPM lentgh is invalide, length :"+split.length+",please connect with manager!";
				insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details);
				resultObj.accumulate("status", status);
				resultObj.accumulate("message", message);
				saveError4Ajax(req, resp, resultObj.toString());
				logger.info(message);
				return;
			}else{
				logger.info("update table upAuthority");
				sql = "update DBPermission set rdPM = ? ,wtPM = ? where topicId = ? and tableName=? and dbName=?";
				List<Object> params = new ArrayList<Object>(); 
				String rdPM = split[1];
				String wtPM = split[2];
				String tableName = split[0];
		        params.add(rdPM); 
		        params.add(wtPM); 
		        params.add(topicId); 
		        params.add(tableName); 
		        params.add(dbName); 
		        details = "update DBPermission set rdPM = "+rdPM+" ,wtPM = "+wtPM+" where topicId = "+topicId+" and tableName= "+tableName+" and dbName="+dbName;

				try {
					dBPermissionDAO.insert(sql, params);
					status = "0";	
					message = "修改主题-表权限成功！";
				} catch (Exception e) {
					status = "1";
					message = "修改主题-表权限失败！";
					details = details+e;
					logger.error("update DBPermission upAuthority fail!"+e);
				}
			}
		}
		insertLog(dBPermissionDAO,userId,roleId,opType,time,status,details); 
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		saveInfo4Ajax(req, resp, resultObj.toString());
		logger.info(message);
	}
	
	
	/**
	 * 查询主题明细
	 * @param req
	 * @param resp
	 * @return
	 * @throws IOException
	 */
	public void searchTablesPM(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		//判断管理员角色
		String opRole = req.getParameter("operRole");
		String userId = req.getParameter("userId");
		String topicId = req.getParameter("topicId");

		String status = "0";
		String message = "获得当前主题Id对应的所有表的权限关系";
		JSONObject resultObj = new JSONObject();
		
		List<dbPermissionDomain> dbPermissionList = dbPermissionService.selectDbPermission(topicId);
		resultObj.accumulate("status", status);
		resultObj.accumulate("data", dbPermissionList);
		
		dbPermissionService.insertLog(opRole, userId, opType, time, status, message);
		saveInfo4Ajax(req, resp, resultObj.toString());
		logger.info(message);
	}
	
	/**
	 * @description 删除角色下的主题 
	 * @author Uriel
	 * @date 2018年2月6日 下午2:19:58
	 * @version V3.0
	 * @param req
	 * @param resp
	 * @param time
	 * @param opType
	 */
	public void deleteRoleRel(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		//判断管理员角色
		String opRole = req.getParameter("operRole");
		String userId = req.getParameter("userId");
		String roleId = req.getParameter("roleId");
		String[] topicIds = req.getParameter("topicId").split(",");

		String status = "0";
		String message = "获得当前主题Id对应的所有表的权限关系";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(opRole)){
			status="1";
			logger.info("have no authority to update table upAuthority");
			message = "have no authority to update table upAuthority";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveError4Ajax(req, resp, resultObj.toString());
			return;
		}
		
		for(int i=0;i < topicIds.length; i++){
			String topicId = topicIds[i];
			roleTopicRel roleTopicList = dbPermissionService.selectRoleTopicList(roleId, topicId);
			if(roleTopicList == null){
				message="角色-主题关系不存在";
			}else{
				String id = roleTopicList.getId();
				dbPermissionService.deleteRoleToleRel(id);
			}
		}
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		dbPermissionService.insertLog(opRole, userId, opType, time, status, message);
		saveInfo4Ajax(req, resp, resultObj.toString());
	}
	
	/**
	 * @description 根据主题、数据库、数据表名称获得permissionId 
	 * @author Uriel
	 * @date 2018年2月7日 上午11:26:56
	 * @version V3.0
	 * @param req
	 * @param resp
	 * @param time
	 * @param opType
	 */
	public void getPermissionId(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		//判断管理员角色
		String opRole = req.getParameter("operRole");
		String userId = req.getParameter("userId");
		String topicId = req.getParameter("topicId");
		String dbName = req.getParameter("dbName");
		String tbName = req.getParameter("tbName");

		String status = "0";
		String message = "获得当前数据库-表对应的所有表的权限关系";
		JSONObject resultObj = new JSONObject();
		
		//根据主题、数据库表的权限关系
		dbPermissionDomain permission = dbPermissionService.selectDbPermission(topicId, dbName, tbName);
		int permissionId = permission.getId();
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("data", permissionId);
		
		dbPermissionService.insertLog(opRole, userId, opType, time, status, message);
		saveInfo4Ajax(req, resp, resultObj.toString());
	}
	
	/**
	 *  插入操作日志
	 * @param dBPermissionDAO
	 * @param roleId
	 * @param opType
	 * @param time
	 * @param status
	 * @param details
	 */
	public void insertLog(DBPermissionDAO dBPermissionDAO,String userId,String roleId,String opType,String time,String status,String details){
		List<Object> logParams = new ArrayList<Object>();
		String logSql = "insert into DBPermLog (userId,roleId,opType,opTime,status,details) values (?,?,?,?,?,?)";
		logParams.add(userId); 
		logParams.add(roleId); 
		logParams.add(opType);
		logParams.add(time); 
		logParams.add(status);
		logParams.add(details);
        try {
			dBPermissionDAO.insert(logSql, logParams);
		} catch (Exception e) {
			logger.error("insert log error"+e);
		} 
	}
	
	/**
	 * @description 获得角色 
	 * @author Uriel
	 * @date 2018年2月4日 下午4:41:14
	 * @version V3.0
	 * @param req
	 * @param resp
	 * @param time
	 * @param opType
	 */
	public void selectRoles(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		String status = "0";
		String message = "";
		String roleId = req.getParameter("operRole");
		String userId = req.getParameter("userId");
		String topicTp = req.getParameter("topicTp");
		
		String current = req.getParameter("current"); //当前页码，从1开始
		JSONObject resultObj = new JSONObject();
		
		// 只有管理员可以查看全部角色
		if(!"0".equals(roleId)){
			status = "1";
			logger.info(roleId+ " have no authority to update table topicDetail ");
			message = roleId + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(req, resp, resultObj.toString());
			return;
		}

		// 判断主题角色，主题角色参数为空时，显示全部主题
		List<roleDetail> roleList = new ArrayList<roleDetail>();
		if(StringUtils.isEmpty(topicTp)){
			roleList = dbPermissionService.selectRoles();
		}else{
			roleList = dbPermissionService.selectRoles(topicTp);
		}
		
		//没有分页的情况下，显示全部角色
		if(StringUtils.isEmpty(current)){
			PAGESIZE = Integer.parseInt(req.getParameter("pagesize")); //每页显示条数
			
			int page = 0;
			if(!StringUtils.isEmpty(current) && !current.equals("undefined")){
				page = Integer.parseInt(req.getParameter("current"));
			}
			resultObj = commonService.jsonProcessing(JSONArray.fromObject(roleList), page, current, PAGESIZE);
		}else{
			resultObj.accumulate("result", roleList);
		}
		
		resultObj.accumulate("status", status);
		dbPermissionService.insertLog(roleId, userId, opType, time, status, message);
		saveInfo4Ajax(req, resp, resultObj.toString());   
	}
	
	/**
	 * @description 新增角色 
	 * @author Uriel
	 * @date 2018年2月4日 下午10:16:27
	 * @version V3.0
	 */
	private void addRole(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		String operRole = req.getParameter("operRole");
		String userId = req.getParameter("userId");
		String roleName = req.getParameter("roleName");
		String roleDesc = req.getParameter("roleDesc");
		String status = "0";
		JSONObject resultObj = new JSONObject();
		String message = "";
		
		if(!"0".equals(operRole)){
			status = "1";
			logger.info(operRole+ " have no authority to update table topicDetail ");
			message = operRole + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(req, resp, resultObj.toString());
			return;
		}
		
		String roleId = UUID.randomUUID().toString();
		try{
			List<roleDetail> roleList = dbPermissionService.selectRoleDetail(roleName);
			if(!roleList.isEmpty()){
				dbPermissionService.deleteRoleDetail(roleName);
			}
			roleDetail roleDetail = new roleDetail();
			roleDetail.setRoleid(roleId);
			roleDetail.setRolename(roleName);
			roleDetail.setRoledesc(roleDesc);
			roleDetail.setCreatetime(time);
			
			dbPermissionService.saveRole(roleDetail);
			message = " insert roleDetail with new role";
		}catch(Exception e){
			status = "1";
			message = e.getMessage();
		}
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		
		dbPermissionService.insertLog(operRole, userId, opType, time, status, message);
		saveInfo4Ajax(req, resp, resultObj.toString()); 
	}	
	
	/**
	 * @description 删除角色 
	 * @author Uriel
	 * @date 2018年2月4日 下午10:27:54
	 * @version V3.0
	 * @param req
	 * @param resp
	 * @param time
	 * @param opType
	 */
	private void deleteRole(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		String operRole = req.getParameter("operRole");
		String delRoleId = req.getParameter("delRoleId");
		String userId = req.getParameter("userId");
		String status = "0";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(operRole)){
			status = "1";
			logger.info(operRole+ " have no authority to update table topicDetail ");
			String message = operRole + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(req, resp, resultObj.toString());
			return;
		}
		
		String message = "";
		try{
			List<roleTopicRel> roleTopicList = dbPermissionService.selectRoleTopicListByRole(delRoleId);
			
			if(roleTopicList.isEmpty()){
				dbPermissionService.deleteRole(delRoleId);
			}else{
				dbPermissionService.deleteRoleTopicRel(delRoleId);
				message = "关联该角色的所有主题已被释放！";
			}
			
		}catch(Exception e){
			status = "1";
			message = e.getMessage();
			logger.error(e.getMessage());
		}
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		
		dbPermissionService.insertLog(operRole, userId, opType, time, status, message);
		saveInfo4Ajax(req, resp, resultObj.toString()); 
	}
	
	/**
	 * @description 更新角色 
	 * @author Uriel
	 * @date 2018年2月4日 下午10:45:38
	 * @version V3.0
	 * @param req
	 * @param resp
	 * @param time
	 * @param opType
	 */
	private void updateRole(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		String operRole = req.getParameter("operRole");
		String roleId = req.getParameter("roleId");
		String userId = req.getParameter("userId");
		String roleName = req.getParameter("roleName");
		String roleDesc = req.getParameter("roleDesc");
		String status = "0";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(operRole)){
			status = "1";
			logger.info(operRole+ " have no authority to update table topicDetail ");
			String message = operRole + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(req, resp, resultObj.toString());
			return;
		}
		String message = "";
		
		List<roleDetail> roleList = dbPermissionService.selectRole(roleId);
		
		if(roleList.isEmpty()){
			status = "1";
			message = "该角色已不存在！";
		}else{
			dbPermissionService.updateRole(roleId, roleName, roleDesc, time);
		}
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		
		dbPermissionService.insertLog(operRole, userId, opType, time, status, message);
		saveInfo4Ajax(req, resp, resultObj.toString()); 
	}
	
	/**
	 * @description 为角色增加主题 
	 * @author Uriel
	 * @date 2018年2月5日 下午4:37:50
	 * @version V3.0
	 * @param req
	 * @param resp
	 * @param time
	 * @param opType
	 */
	private void roleAddTp(HttpServletRequest req, HttpServletResponse resp,String time,String opType){
		String operRole = req.getParameter("operRole");
		String roleId = req.getParameter("roleId");
		String userId = req.getParameter("userId");
		String[] topicIds = req.getParameter("topicIds").split(",");
		String status = "0";
		JSONObject resultObj = new JSONObject();
		
		if(!"0".equals(operRole)){
			status = "1";
			logger.info(operRole+ " have no authority to update table topicDetail ");
			String message = operRole + " have no authority to update table topicDetail ";
			resultObj.accumulate("status", status);
			resultObj.accumulate("message", message);
			saveInfo4Ajax(req, resp, resultObj.toString());
			return;
		}
		
		String message = "";
		for(String topicId : topicIds){
			roleTopicRel roleTopicRel = new roleTopicRel();
			String id = UUID.randomUUID().toString();
			roleTopicRel.setId(id);
			roleTopicRel.setRoleid(roleId);
			roleTopicRel.setTopicid(topicId);
			dbPermissionService.addRoleTopicRel(roleTopicRel);
		}
		
		resultObj.accumulate("status", status);
		resultObj.accumulate("message", message);
		
		dbPermissionService.insertLog(operRole, userId, opType, time, status, message);
		saveInfo4Ajax(req, resp, resultObj.toString()); 
	}
	
	private void closeCon(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				logger.error("close resultSet fail", e);
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				logger.error("close preparedStatement fail", e);
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				logger.error("close connection fail", e);
			}
		}
	}
	
}

